package ru.fadeev.crammer;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import ru.fadeev.crammer.phb.PhrasesBankCallbackHandler;
import ru.fadeev.crammer.phb.PhrasesBankStub;
import ru.fadeev.crammer.phb.PhrasesBankStub.GetPhrases;
import ru.fadeev.crammer.phb.PhrasesBankStub.GetPhrasesResponse;


/**
 * GFIndicesStorage class provides synchronized operations with 
 * phrase-word indices that is used in Gaps Filling Test and with phrases bank
 * tasks. 
 * Gaps Filling test task is to match some words and phrases consisting them.
 * To fasten search of needed phrases there is phrase-word index stored
 * in database. When new word or phrase are added, the task created and 
 * stored in GFIndicesStorage. Then GFIndexer gets tasks from 
 * GFIndicesStorage, builds phrase-word index and saves it to database.
 * GFIndicesStorage also provides functions to get phrases from Phrases Bank
 * (remote server) via web services.
 * @author Konstantin Fadeyev
 *
 */
public class GFIndicesStorage {
	private Vector<IndexerTask> tasks;
	private Vector<Vector<String>> phbTasks;
	private String[] phbResponsePhrases;

	
	/**
	 * Creates instance of GFIndicesStorage
	 */
	public GFIndicesStorage() {
		tasks = new Vector<IndexerTask>();
		phbTasks = new Vector<Vector<String>>();
	}

	
	/**
	 * Inserts phrase-word index into database. Called by GFIndexer.
	 * @param wordId word database id
	 * @param phraseId phrase database id
	 * @param connection opened database connection
	 */
	public synchronized void addGFIndex(int wordId, int phraseId, 
			Connection connection) {
		try {
	    	PreparedStatement insertStatement = connection.prepareStatement(
	    			"INSERT INTO PHRASE_WORD " +
	    			"(PHRASE_ID, WORD_ID) " +
	    			"VALUES (?, ?)");
 
			insertStatement.setInt(1, phraseId);
			insertStatement.setInt(2, wordId);
			insertStatement.executeUpdate();
			insertStatement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	
	/**
	 * Adds new task to GFIndicesStorage. Called when new word or phrase added.
	 * @param task new task
	 */
	public synchronized void addGFIndexerTask(IndexerTask task) {
		if(task.text.length() < 3) {
			return;
		}
		tasks.addElement(task);
		notify();
	}

	
	/**
	 * Adds new phrases bank task to GFIndicesStorage. 
	 * Called when new phrases for some words are needed from phrases bank.
	 * @param words words phrases neede for
	 */
	public void addPHBTask(Vector<String> words) {
		synchronized(phbTasks) {
			phbTasks.addElement(words);
			phbTasks.notify();
		}
	}
	
	
	/**
	 * Deletes phrase-word indices for give phrase(or word) from database.
	 * Non-synchronized function! But should always go inside synhchronized block.
	 * This is because some operations like removing words and indices
	 * (phrases and indices) should be executed inside one synchronized block.
	 * @param id if phrase is true, id is a phrase database id, otherwise id
	 * is a word database id
	 * @param phrase true, if we delete indices for phrase, false - for word
	 * @param connection opened connection
	 */
	public void deleteGFIndices(int id, boolean phrase, 
			 Connection connection) {
		try {
			if(phrase) {
				Statement statement = connection.createStatement();
				statement.executeQuery(
	    			"DELETE FROM PHRASE_WORD " +
	    			"WHERE PHRASE_ID = " + id + "");
				statement.close();
			} else {
				Statement statement = connection.createStatement();
				statement.executeQuery(
	    			"DELETE FROM PHRASE_WORD " +
	    			"WHERE WORD_ID = " + id + "");
				statement.close();

			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Gets task from storage tasks queue. Called by GFIndexer
	 * @return next IndexerTask
	 */
	public synchronized IndexerTask getGFIndexerTask() {
		if(tasks.size() == 0) {
			try {
				wait();
			} catch(Exception e) {
				return null;
			}
		}
		IndexerTask task = (IndexerTask)tasks.elementAt(0);
		tasks.removeElementAt(0);
		return task;
	}
	
	/**
	 * Returns length of storage tasks queue. Called by GFIndexer.
	 * @return length of storage tasks queue
	 */
	public synchronized int getGFIndexerTasksSize() {
		return tasks.size();
	}
	
	/**
	 * Returns length of phrases bank tasks queue. Called by PHBGetter.
	 * @return length of phrases bank tasks queue
	 */
	public synchronized int getPHBTasksSize() {
		return phbTasks.size();
	}
	
	
	/**
	 * Returns phrase id for given word based on earlier created 
	 * phrase-word index. Function looks for phrase-word index with minimal 
	 * test count (for index that was used the least in Gaps Filling Test). 
	 * Called while preparing data for Gaps Filling Test.
	 * @param wordId we looking for phrase containing word with this id 
	 * @param connection opened database connection
	 * @return phrase id
	 */
	public synchronized int getPhraseId(int wordId, Connection connection) {
		try {			
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(
	    			"SELECT TOP 1 PHRASE_ID " +
	    			"FROM PHRASE_WORD " +
	    			"WHERE WORD_ID = " + wordId + 
	    				" AND TEST_COUNT = (SELECT MIN(TEST_COUNT) " +
	    									"FROM PHRASE_WORD " +
	    									"WHERE WORD_ID = "+ wordId+")"
	    			);	
        	if(resultSet.next()) {
        		return resultSet.getInt(1);
        	}
        	statement.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }
		return -1;
	}
	
	
	/**
	 * Increments test count for given phrase-word index. Called after 
	 * Gaps Filling Test to mark that this phrase-word pair was already used
	 * for test, so next time function getPhraseId returns another phrase. 
	 * @param wordId wordId, that with phraseId identifies index
	 * @param phraseId phraseId, that with wordId identifies index
	 */
	public synchronized void incrementTestCount(int wordId, int phraseId) {
		try {
			Connection connection = DatabaseManager.getConnection(); 
			Statement statement = connection.createStatement();
    		statement.executeUpdate(
    				"UPDATE PHRASE_WORD " +
    				"SET TEST_COUNT = (TEST_COUNT + 1) " +
    				" WHERE WORD_ID = " + wordId + 
    					" AND PHRASE_ID = " + phraseId);
    		statement.close();
    		connection.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }
	}
	
	
	/**
	 * Loads storage tasks queue from database. Called when Crammer starts.
	 */
	public synchronized void loadGFIndexerTasks() {
		try {			
			Connection connection = DatabaseManager.getConnection();
			Statement statement = connection.createStatement();;
			ResultSet resultSet = statement.executeQuery(
	    			"SELECT TASK_ID, PHRASE, INSERTED, TASK_TEXT " +
	    			"FROM INDEXER_TASK"
	    			);	
        	while(resultSet.next()) {
        		int taskId = resultSet.getInt(1);
        		boolean phrase = resultSet.getBoolean(2);
        		boolean inserted = resultSet.getBoolean(3);
        		String text = resultSet.getString(4);
        		IndexerTask task;
        		if(phrase) {
        			task = new IndexerPhraseTask(taskId, text, inserted);
        		} else {
        			task = new IndexerWordTask(taskId, text, inserted);
        		}
    			tasks.addElement(task);
        	}
        	statement.executeQuery("DELETE FROM INDEXER_TASK");
        	statement.close();
        	connection.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }
	}
	

	/**
	 * Loads phrases bank tasks queue from database. Called when Crammer starts.
	 */
	public void loadPHBTasks() {
		synchronized(phbTasks) {
			try {			
				Connection connection = DatabaseManager.getConnection();
				Statement statement = connection.createStatement();
				ResultSet resultSet = statement.executeQuery(
		    			"SELECT TASK_NUMBER, WORD " +
		    			"FROM PHB_TASK ORDER BY TASK_NUMBER"
		    			);	
				int currentTaskNumber = 0;
				Vector<String> currentTask = new Vector<String>();
	        	while(resultSet.next()) {
	        		int taskNumber = resultSet.getInt(1);
	        		String wordText = resultSet.getString(2);
	        		if(taskNumber != currentTaskNumber) {
	        			phbTasks.addElement(currentTask);
	        			currentTaskNumber++;
	        			currentTask = new Vector<String>();
	        		}
        			currentTask.addElement(wordText);
	        	}
	        	if(currentTask.size() != 0) {
	        		phbTasks.addElement(currentTask);
	        	}
	        	statement.executeQuery("DELETE FROM PHB_TASK");
	        	statement.close();
	        	connection.close();
			} catch (Exception e) {
		        e.printStackTrace();
		    }
		}
	}
	
	
	/**
	 * Gets task from phrases bank tasks queue, sends request to PhrasesBank
	 * server, wait for response - phrases array. Then adds received phrases to
	 * PhrasesCorpus. If all ok - removes processed tasks from phrases bank task
	 * queue.
	 */
	public void processPHBTask() {
		final Vector<String> currentTask;
		synchronized(phbTasks) {
			if(phbTasks.size() == 0) {
				try {
					phbTasks.wait();
				} catch(InterruptedException e) {
					return;
				}
			}
		}
		currentTask = phbTasks.elementAt(0);
		synchronized(currentTask) {
			try {
				String url = "http://" 
					+ Settings.getString("phrasesBankServer") +
					":" + Settings.getString("phrasesBankPort") + 
					"/PhrasesBank/services/PhrasesBank";
				boolean useProxy = Settings.getBoolean("phrasesBankUseProxy");
				String proxyHost;
	        	int proxyPort;
	        	if(useProxy) {
	        		proxyHost = Settings.getString("phrasesBankProxyServer");
	        		proxyPort = Integer.parseInt(
	        				Settings.getString("phrasesBankProxyPort"));
	        	} else {
	        		proxyHost = "";
					proxyPort = 0;
	        	}
				PhrasesBankStub stub = new PhrasesBankStub(url, useProxy, 
						proxyHost, proxyPort);
				GetPhrases getPhrases = new GetPhrases();
				
				String[] stringArray = new String[currentTask.size()];
				System.arraycopy(currentTask.toArray(), 0, stringArray, 
						0, currentTask.size());
				getPhrases.setWords(stringArray);
				getPhrases.setPhraseLimit(30);
				PhrasesBankCallbackHandler callBackHandler = 
					new PhrasesBankCallbackHandler() {
				    public void receiveResultgetPhrases(GetPhrasesResponse result) {
				    	try {				
				    		phbResponsePhrases = result.get_return();
							synchronized(currentTask) {
								currentTask.notify();
							}
				    	}catch (Exception e) {
				    		e.printStackTrace();
				        }
				    }
				};
				try {	    	
					stub.startgetPhrases(getPhrases, callBackHandler);
				} catch(Exception e) {
					// failed to connect
					e.printStackTrace();
					return;
				}
				try {
					phbResponsePhrases = null;
					currentTask.wait(Settings.PHRASES_BANK_REQUEST_TIMEOUT);
				} catch(InterruptedException e) {
					return;
				}
				
				if(phbResponsePhrases != null) {
					int batchId;
					Connection connection = DatabaseManager.getConnection(); 
					Statement statement = connection.createStatement();
					String remoteBatchName = 
						Settings.getString("phrasesBankServer");
					ResultSet resultSet = statement.executeQuery(
			    			"SELECT ID " +
			    			"FROM PHRASES_BATCH " +
			    			"WHERE NAME='"+ remoteBatchName +"'"
			    			);	
					if(!resultSet.next()) {
						statement.executeUpdate(
							"INSERT INTO PHRASES_BATCH " +
								"(name, " +
									"create_date) " +
								"VALUES ('" + remoteBatchName 
									+ "', CURRENT_TIMESTAMP)");		    	
				    	
						CallableStatement identityStatement = 
							connection.prepareCall("{?= CALL IDENTITY()}");
						resultSet = identityStatement.executeQuery();	
						resultSet.next();
						batchId = resultSet.getInt(1);			
						identityStatement.close();
					} else {
						batchId = resultSet.getInt(1);
					}
					
					PreparedStatement isThisPhraseStatement = 
						connection.prepareStatement(
		    				"SELECT ID " +
			    			"FROM PHRASE " +
			    			"WHERE PHRASE_TEXT LIKE ?"
		    				);	  
					
			    	PreparedStatement phraseStatement = 
			    		connection.prepareStatement(
			    			"INSERT INTO PHRASE " +
			    			"(BATCH_ID, PHRASE_TEXT) " +
			    			"VALUES ("+ batchId +",?)");
			    	
			    	CallableStatement identityStatement = 
			    		connection.prepareCall("{?= CALL IDENTITY()}");
			    	for(int i = 0; i < phbResponsePhrases.length; i++) {  		  
			    		isThisPhraseStatement.setString(1, phbResponsePhrases[i]);
						resultSet = isThisPhraseStatement.executeQuery();
				    	if(!resultSet.next()) {		    		
				    		phraseStatement.setString(1, phbResponsePhrases[i]);
				    		phraseStatement.executeUpdate();
				    		resultSet = identityStatement.executeQuery();	
							resultSet.next();
							int id = resultSet.getInt(1);
							IndexerPhraseTask phraseTask = new IndexerPhraseTask(
									id, phbResponsePhrases[i], true);
							addGFIndexerTask(phraseTask);
				    	}		    	
			    	}
			    	identityStatement.close();
			    	phraseStatement.close();
			    	isThisPhraseStatement.close();
					statement.close();
					connection.close();
				}
				synchronized(phbTasks) {
					phbTasks.removeElementAt(0);
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		
	}
	
	
	/**
	 * Saves current tasks queue to database. Used when user wants to exit
	 * Crammer, but storage queue is still not empty. Next time Crammer starts
	 * saved queue will be loaded and GFIndexer will continue its work. 
	 */
	public synchronized void saveGFIndexerTasks() {
		try {
			Connection connection = DatabaseManager.getConnection();  
	    	PreparedStatement insertStatement = connection.prepareStatement(
	    			"INSERT INTO INDEXER_TASK " +
	    			"(TASK_ID, PHRASE, INSERTED, TASK_TEXT) " +
	    			"VALUES (?, ?, ?, ?)");
	    	for(int i = 0; i < tasks.size(); i++) {
	    		IndexerTask task = (IndexerTask)tasks.elementAt(i);
	    		if(task instanceof IndexerPhraseTask) {
	    			insertStatement.setInt(1, 
	    					((IndexerPhraseTask)task).getPhraseId());
	    			insertStatement.setBoolean(2, true);
	    		} else {
	    			insertStatement.setInt(1, 
	    					((IndexerWordTask)task).getWordId());
	    			insertStatement.setBoolean(2, false);
	    		}
	    		insertStatement.setBoolean(3, task.inserted);
	    		insertStatement.setString(4, task.text);	
	    		insertStatement.addBatch();
	    	}
			insertStatement.executeBatch();
			insertStatement.close();
			connection.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Saves phrases bank tasks queue to database. Used when user wants to exit
	 * Crammer, but phrases bank tasks queue is still not empty. 
	 * Next time Crammer starts saved queue will be loaded and PHBGetter 
	 * will continue its work. 
	 */
	public void savePHBTasks() {
		synchronized(phbTasks) {
			try {
				Connection connection = DatabaseManager.getConnection();  
		    	PreparedStatement insertStatement = connection.prepareStatement(
		    			"INSERT INTO PHB_TASK " +
		    			"(TASK_NUMBER, WORD) " +
		    			"VALUES (?, ?)");
		    	for(int i = 0; i < phbTasks.size(); i++) {
		    		Vector<String> task = phbTasks.elementAt(i);
		    		for(int j = 0; j < task.size(); j++) {
		    			insertStatement.setInt(1, i);
		    			insertStatement.setString(2, task.elementAt(j));
		    			insertStatement.addBatch();
		    		}	    				    		
		    	}
				insertStatement.executeBatch();
				insertStatement.close();
				connection.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	/**
	 * Tests connection to Phrases Bank server
	 * @param server Phrases Bank server host
	 * @param port Phrases Bank server port
	 * @return true, if it was successfully connected.
	 */
	public boolean testPhrasesBankConnection(String server, String port, 
			boolean useProxy, String proxyHost, int proxyPort) {
		try {
			String url = "http://" + server +
					":" + port + 
					"/PhrasesBank/services/PhrasesBank";
			PhrasesBankStub stub = new PhrasesBankStub(url,useProxy,
					proxyHost, proxyPort);
			GetPhrases getPhrases = new GetPhrases();
		
			String[] stringArray = {"testword"};	
			getPhrases.setWords(stringArray);
			getPhrases.setPhraseLimit(1);
			try {
				stub.getPhrases(getPhrases);
			} catch (Exception e) {
				// failed to connect
				return false;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
}
